﻿using Dapper;
using DBOptionPro.Model;
using DevExpress.ClipboardSource.SpreadsheetML;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBOptionPro.DAL
{
    /// <summary>
    /// 同步数据数据库访问类(mysql)
    /// </summary>
    public class MyData_ServerDAL : IDisposable
    {
        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="option">系统参数信息对象</param>
        public MyData_ServerDAL(SysOption option)
        {
            _SysOption = option;
            string conStr = $"Database={_SysOption.DBName};Data Source={_SysOption.IP};User Id={_SysOption.User};Password={_SysOption.Pwd};CharSet=utf8;port={_SysOption.Port}";
            _DBConnection = new MySqlConnection(conStr);
        }
        /// <summary>
        /// 系统参数信息对象
        /// </summary>
        private SysOption _SysOption;
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        protected readonly IDbConnection _DBConnection;

        /// <summary>
        /// 获取语文成绩字段
        /// </summary>
        /// <param name="areaCode">区域代码</param>
        /// <returns></returns>
        public string[] GetYWFields(string areaCode)
        {
            string sql = $"select yuwen from ly_tdd_lutfield where year = @year and code = @code";
            var field = _DBConnection.ExecuteScalar<string>(sql, new { year = _SysOption.Year, code = areaCode });
            return field.Split('%');
        }

        /// <summary>
        /// 检查考生号是否存在
        /// </summary>
        /// <param name="ksh"></param>
        /// <returns></returns>
        public bool Exists(string ksh)
        {
            string sql = "select 1 from ly_tdd_stu where ksh = @ksh limit 1";
            var ret = _DBConnection.ExecuteScalar<bool>(sql, new { ksh = ksh });
            return ret;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<bool> Update(MyData data)
        {
            // todo 这里就同步改成mysql数据库人语句得自行修改对应的同步sql语句（更新）
            string sql;
            sql = "update ly_tdd_stu set zslqztdm=@zslqztdm, year=@year, xm=@xm, xb=@xb, xbdm=@xbdm, zsdiqu=@zsdiqu, zsdiqudm=@zsdiqudm, kl=@kl, kldm=@kldm, zjhm=@zjhm, tzcj=@tzcj,tdcj=@tdcj,tdcjqz=@tdcjqz,wyyz=@wyyz,yyky=@yyky,wytl=@wytl,wyks=@wyks,kstc=@kstc,tddw=@tddw,fctj=@fctj,tjlx=@tjlx,zkzh=@zkzh,csrq=@csrq,zzmm=@zzmm,zzmmdm=@zzmmdm,mz=@mz,mzdm=@mzdm,pc=@pc,pcdm=@pcdm,jhxz=@jhxz,jhxzdm=@jhxzdm,jhlb=@jhlb,jhlbdm=@jhlbdm,kslb=@kslb,kslbdm=@kslbdm,bylb=@bylb,bylbdm=@bylbdm,byxxmc=@byxxmc,byxxdm=@byxxdm,ysjz=@ysjz,bmdw=@bmdw ,ksdq=@ksdq,ksdqdm=@ksdqdm,kshj=@kshj,kshjdm=@kshjdm,zjlx=@zjlx,xjh=@xjh,txdz=@txdz,lxsj=@lxsj,lxdh=@lxdh,sjr=@sjr,yzbm=@yzbm,ksjlhcf=@ksjlhcf,sxpdkhyj=@sxpdkhyj,tjjl=@tjjl,tddbh=@tddbh,bz=@bz,zytz=@zytz,ytzy=@ytzy,lqsj=@lqsj,lqlx=@lqlx,lqlxdm=@lqlxdm,lqzy=@lqzy,lqzydh=@lqzydh,lqzydm=@lqzydm,xz=@xz,zyh=@zyh,zgf=@zgf,yxdycj=@yxdycj,zyytdxjcj=@zyytdxjcj,zyytjzcj=@zyytjzcj,zyytfjcj=@zyytfjcj,kszg=@kszg,hjlb=@hjlb,czlb=@czlb,xkkm=@xkkm,jhid=@jhid,skkm=@skkm,tjsxbz=@tjsxbz,tdlx=@tdlx,tdlxdm=@tdlxdm,tdsj=@tdsj,cj=@cj,zh=@zh,yw=@yw,sx=@sx,wy=@wy,wl=@wl,hx=@hx,sw=@sw,zz=@zz,ls=@ls,dl=@dl,js=@js,zhuanyeke=@zhuanyeke,smkx=@smkx,minzuyuwen=@minzuyuwen,qgxzcjf=@qgxzcjf,cc=@cc,ccdm=@ccdm,tfpw=@tfpw,zyzy1=@zyzy1,zyzydm1=@zyzydm1,zyzy2=@zyzy2,zyzydm2=@zyzydm2,zyzy3=@zyzy3,zyzydm3=@zyzydm3,zyzy4=@zyzy4,zyzydm4=@zyzydm4,zyzy5=@zyzy5,zyzydm5=@zyzydm5,zyzy6=@zyzy6,zyzydm6=@zyzydm6,updatetime=@updatetime   where ksh= @ksh";
            //sql = "update ly_tdd_stu set year=@year, xm=@xm, xb=@xb, xbdm=@xbdm, zsdiqu=@zsdiqu, zsdiqudm=@zsdiqudm, kl=@kl, kldm=@kldm, zjhm=@zjhm, tzcj=@tzcj,tdcj=@tdcj,tdcjqz=@tdcjqz,yyky=@yyky,wytl=@wytl,wyks=@wyks,qgxzcjf=@qgxzcjf,kstc=@kstc,tddw=@tddw,fctj=@fctj,tjlx=@tjlx,zkzh=@zkzh,csrq=@csrq,zzmm=@zzmm,zzmmdm=@zzmmdm,mz=@mz,mzdm=@mzdm,pc=@pc,pcdm=@pcdm,jhxz=@jhxz,jhxzdm=@jhxzdm,kslb=@kslb,kslbdm=@kslbdm,bylb=@bylb,bylbdm=@bylbdm,byxxmc=@byxxmc,byxxdm=@byxxdm,ysjz=@ysjz,bmdw=@bmdw ,ksdq=@ksdq,ksdqdm=@ksdqdm,kshj=@kshj,kshjdm=@kshjdm,yw=@yw where ksh= @ksh";
            //if (string.IsNullOrWhiteSpace(data.lqsj))
            //{
            //  data.lqsj = null;
            // }
            return await _DBConnection.ExecuteAsync(sql, data) == 1;
        }

        public int UpdateDBF(MyData data)
        {
            string sql = "SELECT count(1) FROM ly_tdd_stu WHERE ksh=@ksh";
            int count = _DBConnection.ExecuteScalar<int>(sql, new { data.ksh });
            if (count > 0)
            {
                sql = "update ly_tdd_stu set xm=@xm where ksh= @ksh";
                return _DBConnection.Execute(sql, new { data.ksh, data.xm });
            }
            else
            {
                return 0;
            }

        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<bool> Insert(MyData data)
        {
            // todo 这里就同步改成mysql数据库人语句得自行修改对应的同步sql语句(插入)
            string sql;
            sql = "insert into ly_tdd_stu (zslqztdm, year, ksh, xm, xb, xbdm,zsdiqu, zsdiqudm, kl, kldm, zjhm,tzcj,tdcj,tdcjqz,wyyz,yyky,wytl,wyks,kstc,tddw,fctj,tjlx,zkzh,csrq,zzmm,zzmmdm,mz,mzdm,pc,pcdm,jhxz,jhxzdm,jhlb,jhlbdm,kslb,kslbdm,bylb,bylbdm,byxxmc,byxxdm,ysjz,bmdw ,ksdq,ksdqdm,kshj,kshjdm,zjlx,xjh,txdz,lxsj,lxdh,sjr,yzbm,ksjlhcf,sxpdkhyj,tjjl,tddbh,bz,zytz,ytzy,lqsj,lqlx,lqlxdm,lqzy,lqzydh,lqzydm,xz,zyh,zgf,yxdycj,zyytdxjcj,zyytjzcj,zyytfjcj,kszg,hjlb,czlb,xkkm,jhid,skkm,tjsxbz,tdlx,tdlxdm,tdsj,cj,zh,yw,sx,wy,wl,hx,sw,zz,ls,dl,js,zhuanyeke,smkx,minzuyuwen,qgxzcjf,cc,ccdm,tfpw,zyzy1,zyzydm1,zyzy2,zyzydm2,zyzy3,zyzydm3,zyzy4,zyzydm4,zyzy5,zyzydm5,zyzy6,zyzydm6,createtime,updatetime) values (@zslqztdm, @year, @ksh, @xm, @xb, @xbdm, @zsdiqu, @zsdiqudm, @kl, @kldm , @zjhm,@tzcj,@tdcj,@tdcjqz,@wyyz,@yyky,@wytl,@wyks,@kstc,@tddw,@fctj,@tjlx,@zkzh,@csrq,@zzmm,@zzmmdm,@mz,@mzdm,@pc,@pcdm,@jhxz,@jhxzdm,@jhlb,@jhlbdm,@kslb,@kslbdm,@bylb,@bylbdm,@byxxmc,@byxxdm,@ysjz,@bmdw ,@ksdq,@ksdqdm,@kshj,@kshjdm,@zjlx,@xjh,@txdz,@lxsj,@lxdh,@sjr,@yzbm,@ksjlhcf,@sxpdkhyj,@tjjl,@tddbh,@bz,@zytz,@ytzy,@lqsj,@lqlx,@lqlxdm,@lqzy,@lqzydh,@lqzydm,@xz,@zyh,@zgf,@yxdycj,@zyytdxjcj,@zyytjzcj,@zyytfjcj,@kszg,@hjlb,@czlb,@xkkm,@jhid,@skkm,@tjsxbz,@tdlx,@tdlxdm,@tdsj,@cj,@zh,@yw,@sx,@wy,@wl,@hx,@sw,@zz,@ls,@dl,@js,@zhuanyeke,@smkx,@minzuyuwen,@qgxzcjf,@cc,@ccdm,@tfpw,@zyzy1,@zyzydm1,@zyzy2,@zyzydm2,@zyzy3,@zyzydm3,@zyzy4,@zyzydm4,@zyzy5,@zyzydm5,@zyzy6,@zyzydm6,@createtime,@updatetime)";
            //sql = "insert into ly_tdd_stu (year, ksh, xm, xb, xbdm,zsdiqu, zsdiqudm, kl, kldm, zjhm,tzcj,tdcj,tdcjqz,yyky,wytl,wyks,qgxzcjf,kstc,tddw,fctj,tjlx,zkzh,csrq,zzmm,zzmmdm,mz,mzdm,pc,pcdm,jhxz,jhxzdm,kslb,kslbdm,bylb,bylbdm,byxxmc,byxxdm,ysjz,bmdw ,ksdq,ksdqdm,kshj,kshjdm,yw) values (@year, @ksh, @xm, @xb, @xbdm, @zsdiqu, @zsdiqudm, @kl, @kldm , @zjhm,@tzcj,@tdcj,@tdcjqz,@yyky,@wytl,@wyks,@qgxzcjf,@kstc,@tddw,@fctj,@tjlx,@zkzh,@csrq,@zzmm,@zzmmdm,@mz,@mzdm,@pc,@pcdm,@jhxz,@jhxzdm,@kslb,@kslbdm,@bylb,@bylbdm,@byxxmc,@byxxdm,@ysjz,@bmdw ,@ksdq,@ksdqdm,@kshj,@kshjdm, @yw)";
            //string sql = "insert into ly_tdd_stu (ksh, xm) values (@ksh, @xm)";
            // if(string.IsNullOrWhiteSpace(data.lqsj))
            //{
            //   data.lqsj = null;
            //}
            return await _DBConnection.ExecuteAsync(sql, data) == 1;
        }

        /// <summary>
        /// 同步完一个本地库后更新ly_tdd_syn表
        /// </summary>
        /// <param name="areaCode"></param>
        /// <param name="time"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public bool UpdateSyncTable(string areaCode, DateTime? time, string content)
        {
            string sql = "update ly_tdd_syn set syntime=@syntime,content=@content where code=@code";
            return _DBConnection.Execute(sql, new { syntime = time, content = content, code = areaCode }) == 1;
        }

        public void Dispose()
        {
            _DBConnection?.Close();
        }

        private DataTable GetDataTable(string sql, object pars = null)
        {
            DataTable dt = new DataTable();
            _DBConnection.Open();
            var ret = _DBConnection.ExecuteReader(sql, pars);
            dt.Load(ret);
            return dt;
        }

        /// <summary>
        /// 获取成绩字段
        /// </summary>
        /// <param name="areaCode">区域代码</param>
        /// <returns></returns>
        public Dictionary<string, string[]> GetSourFields(string areaCode)
        {
            Dictionary<string, string[]> dRet = new Dictionary<string, string[]>();
            string sql = $"select zongfen, zonghe, yuwen, shuxue, waiyu, wuli, huaxue, shengwu, shengmingkexue, zhengzhi, lishi, dili, jishu, zhuanyeke, minzuyuwen, qgxzcjf from ly_tdd_lutfield where year = @year and code = @code";
            var dt = GetDataTable(sql, new { year = _SysOption.Year, code = areaCode });
            if (dt.Rows.Count == 0) return null;

            string cjName = "zongfen";
            string tempStr = dt.Rows[0][cjName]?.ToString();
            List<string> tempFields = null;
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "zonghe";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "yuwen";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "shuxue";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "waiyu";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "wuli";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "huaxue";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "shengwu";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "shengmingkexue";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "zhengzhi";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "lishi";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "dili";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "jishu";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "zhuanyeke";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }

            cjName = "minzuyuwen";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }
            cjName = "qgxzcjf";
            tempStr = dt.Rows[0][cjName]?.ToString();
            if (string.IsNullOrWhiteSpace(tempStr))
            {
                dRet.Add(cjName, null);
            }
            else
            {
                tempFields = tempStr.Split('%').ToList();
                tempFields.RemoveAll(t => string.IsNullOrWhiteSpace(t));
                dRet.Add(cjName, tempFields.ToArray());
            }
            return dRet;
        }
    }
}
